import os
from datetime import datetime, timezone
from typing import Optional, List

from pydantic import BaseModel, field_serializer
from sqlalchemy import Column, Integer, String, Text, TIMESTAMP, SmallInteger, text, bindparam
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.sql import func

from Logs.log_handle import get_log
from tools.db_model_tool import transactional, db_session

# SQLAlchemy 基础模型声明
Base = declarative_base()
# 日志记录器
log = get_log(os.path.split(__file__)[-1])


# ----------------------------- 数据库模型 -----------------------------
class LocalVariable(Base):
    __tablename__ = "tb_local_variables"
    id = Column(Integer, primary_key=True, index=True, comment="自增主键")
    local_name = Column(String(255), nullable=True, comment="变量所属的组名")
    local_key = Column(String(255), nullable=True, comment="变量名称")
    local_value = Column(Text, nullable=True, comment="变量值")
    remark = Column(String(255), nullable=True, comment="备注")
    is_del = Column(SmallInteger, default=1, comment="逻辑删除：1-存在，0-删除")
    created_at = Column(TIMESTAMP, server_default=func.now(), comment="创建时间")
    updated_at = Column(TIMESTAMP, server_default=func.now(), onupdate=func.now(), comment="更新时间")


class GlobalVariable(Base):
    __tablename__ = "tb_global_variables"
    id = Column(Integer, primary_key=True, index=True, comment="自增主键")
    global_name = Column(String(255), nullable=True, comment="全局变量名称")
    global_key = Column(String(255), nullable=True, unique=True, comment="变量名称")
    global_value = Column(Text, nullable=True, comment="变量值")
    remark = Column(String(255), nullable=True, comment="备注")
    is_del = Column(SmallInteger, default=1, comment="逻辑删除：1-存在，0-删除")
    created_at = Column(TIMESTAMP, server_default=func.now(), comment="创建时间")
    updated_at = Column(TIMESTAMP, server_default=func.now(), onupdate=func.now(), comment="更新时间")


# ----------------------------- 请求体模型 -----------------------------
class LocalVariableCreate(BaseModel):
    local_name: Optional[str] = None
    local_key: Optional[str] = None
    local_value: Optional[str] = None
    remark: Optional[str] = None

    class Config:
        from_attributes = True


class GlobalVariableCreate(BaseModel):
    global_name: Optional[str] = None
    global_key: Optional[str] = None
    global_value: Optional[str] = None
    remark: Optional[str] = None

    class Config:
        from_attributes = True


# ----------------------------- 响应体模型 -----------------------------
class LocalVariableResponse(BaseModel):
    id: int
    local_name: Optional[str]
    local_key: Optional[str]
    local_value: Optional[str]
    remark: Optional[str]
    created_at: Optional[datetime]
    updated_at: Optional[datetime]

    @field_serializer("created_at", "updated_at", check_fields=False)
    def serialize_datetime(self, dt: Optional[datetime]) -> Optional[str]:
        return dt.strftime("%Y-%m-%d %H:%M:%S") if dt else None

    class Config:
        from_attributes = True


class GlobalVariableResponse(BaseModel):
    id: int
    global_name: Optional[str]
    global_key: Optional[str]
    global_value: Optional[str]
    remark: Optional[str]
    created_at: Optional[datetime]
    updated_at: Optional[datetime]

    @field_serializer("created_at", "updated_at", check_fields=False)
    def serialize_datetime(self, dt: Optional[datetime]) -> Optional[str]:
        return dt.strftime("%Y-%m-%d %H:%M:%S") if dt else None

    class Config:
        from_attributes = True


# ----------------------------- 原生 SQL CRUD: Local -----------------------------
@transactional
def create_localvariable_in_db(local: LocalVariableCreate, db: Session) -> dict:
    now = datetime.now(timezone.utc)
    sql = text("""
        INSERT INTO tb_local_variables
          (local_name, local_key, local_value, remark, is_del, created_at, updated_at)
        VALUES
          (:local_name, :local_key, :local_value, :remark, 1, :created_at, :updated_at)
    """)
    params = local.dict()
    params.update({"created_at": now, "updated_at": now})
    res = db.execute(sql, params)
    return {"message": "添加成功", "id": res.lastrowid}


@db_session
def select_localvariable_in_db(page: int, size: int, local: LocalVariableCreate, db: Session) -> dict:
    filters, params = [], {}
    for field, val in local.dict().items():
        if val:
            filters.append(f"{field} LIKE :{field}")
            params[field] = f"%{val}%"
    where = " AND ".join(filters)
    clause = f"WHERE is_del=1{' AND ' + where if where else ''}"
    total = db.execute(text(f"SELECT COUNT(*) FROM tb_local_variables {clause}"), params).scalar()
    params.update({"offset": (page - 1) * size, "limit": size})
    rows = db.execute(text(f"SELECT * FROM tb_local_variables {clause} ORDER BY id ASC LIMIT :offset,:limit"),
                      params).mappings().all()
    return {"total": total, "page": page, "size": size,
            "data": [LocalVariableResponse.from_orm(r) for r in rows]}


@transactional
def del_localvariables_id_and_ids(ids: List[int], db: Session) -> dict:
    if not ids: return {"message": "未提供 ids"}
    now = datetime.now(timezone.utc)
    sql = text("""
        UPDATE tb_local_variables
        SET is_del=0, updated_at=:updated_at
        WHERE id IN :ids
    """).bindparams(bindparam("ids", expanding=True))
    res = db.execute(sql, {"ids": ids, "updated_at": now})
    return {"message": f"删除 {res.rowcount} 条"}


@transactional
def update_localvariables_in_db(var: LocalVariableResponse, db: Session) -> dict:
    now = datetime.now(timezone.utc)
    data = var.dict(exclude_unset=True)
    vid = data.pop("id", None)
    data.pop("created_at", None);
    data.pop("updated_at", None)
    setcl = ",".join([f"{k}=:${k}" for k in data])
    sql = text(f"""
        UPDATE tb_local_variables
        SET {setcl}, updated_at=:updated_at
        WHERE id=:vid
    """)
    params = {**data, "vid": vid, "updated_at": now}
    res = db.execute(sql, params)
    return {"message": f"更新 {res.rowcount} 条"}


# ----------------------------- 原生 SQL CRUD: Global -----------------------------
@transactional
def create_globalvariable_in_db(global_var: GlobalVariableCreate, db: Session) -> dict:
    now = datetime.now(timezone.utc)
    sql = text("""
        INSERT INTO tb_global_variables
          (global_name, global_key, global_value, remark, is_del, created_at, updated_at)
        VALUES
          (:global_name, :global_key, :global_value, :remark, 1, :created_at, :updated_at)
    """)
    params = global_var.dict()
    params.update({"created_at": now, "updated_at": now})
    res = db.execute(sql, params)
    return {"message": "添加成功", "id": res.lastrowid}


@db_session
def select_globalvariable_in_db(page: int, size: int, global_var: GlobalVariableCreate, db: Session) -> dict:
    filters, params = [], {}
    for field, val in global_var.dict().items():
        if val:
            filters.append(f"{field} LIKE :{field}")
            params[field] = f"%{val}%"
    where = " AND ".join(filters)
    clause = f"WHERE is_del=1{' AND ' + where if where else ''}"
    total = db.execute(text(f"SELECT COUNT(*) FROM tb_global_variables {clause}"), params).scalar()
    params.update({"offset": (page - 1) * size, "limit": size})
    rows = db.execute(text(f"SELECT * FROM tb_global_variables {clause} ORDER BY id ASC LIMIT :offset,:limit"),
                      params).mappings().all()
    return {"total": total, "page": page, "size": size,
            "data": [GlobalVariableResponse.from_orm(r) for r in rows]}


@transactional
def del_globalvariables_id_and_ids(global_ids: List[int], db: Session) -> dict:
    if not global_ids: return {"message": "未提供 global_ids"}
    now = datetime.now(timezone.utc)
    sql = text("""
        UPDATE tb_global_variables
        SET is_del=0, updated_at=:updated_at
        WHERE id IN :ids
    """).bindparams(bindparam("ids", expanding=True))
    res = db.execute(sql, {"ids": global_ids, "updated_at": now})
    return {"message": f"删除 {res.rowcount} 条"}


@transactional
def update_globalvariables_in_db(var: GlobalVariableResponse, db: Session) -> dict:
    now = datetime.now(timezone.utc)
    data = var.dict(exclude_unset=True)
    gid = data.pop("id", None)
    data.pop("created_at", None);
    data.pop("updated_at", None)
    setcl = ",".join([f"{k}=:${k}" for k in data])
    sql = text(f"""
        UPDATE tb_global_variables
        SET {setcl}, updated_at=:updated_at
        WHERE id=:gid
    """)
    params = {**data, "gid": gid, "updated_at": now}
    res = db.execute(sql, params)
    return {"message": f"更新 {res.rowcount} 条"}
